import pymysql
from pymysql.cursors import DictCursor
from DBUtils.PooledDB import PooledDB

sql_setting = {'mysql': {'host': 'localhost', 'port': 3306, 'user': 'root', 'passwd': 'summit-1',
                         'db': 'mydb'}}


class MysqlUtil(object):
    __pool = {}

    def __init__(self, conf_name='mysql'):
        self._conn = MysqlUtil.__get_conn(conf_name)
        self._cursor = self._conn.cursor()

        self._cursor.execute('SET NAMES utf8mb4')
        self._cursor.execute('SET CHARACTER SET utf8mb4')
        self._cursor.execute('SET character_set_connection=utf8mb4')

    @classmethod
    def __get_conn(cls, conf_name):
        if conf_name not in MysqlUtil.__pool:
            print('Create pool for {}'.format(conf_name))
            MysqlUtil.__pool[conf_name] = PooledDB(
                creator=pymysql,
                mincached=1,
                maxcached=20,
                use_unicode=True,
                charset='utf8',
                cursorclass=DictCursor,
                **sql_setting[conf_name]
            )
        return MysqlUtil.__pool[conf_name].connection()

    def close(self):
        if self._cursor:
            self._cursor.close()
        self._conn.close()

    def insert_one(self, sql, value):
        return self._cursor.execute(sql, value)

    def insert_many(self, sql, value):
        return self._cursor.executemany(sql, value)

    def update(self, sql, param=None):
        return self._cursor.execute(sql, param)

    def fetch_all(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchall()
        else:
            result = False
        return result

    def fetch_one(self, sql, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchone()
        else:
            result = False
        return result

    def fetch_many(self, sql, num, param=None):
        if param is None:
            count = self._cursor.execute(sql)
        else:
            count = self._cursor.execute(sql, param)
        if count > 0:
            result = self._cursor.fetchmany(num)
        else:
            result = False
        return result

